﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Web.Configuration;

/// <summary>
/// DB 的摘要说明
/// </summary>
public class DB
{
    public MySqlConnection mysqlcon;
    public static DataSet ds=new DataSet ();
    public static Random ran = new Random();
    string TableName=null;

    public DB()
    {
        //
        // TODO: 在此处添加构造函数逻辑
        //
        try
        {
            string constring = WebConfigurationManager.ConnectionStrings["mycon"].ConnectionString;
            mysqlcon = new MySqlConnection(constring);
        }
        catch (Exception e)
        {

            System.Web.HttpContext.Current.Response.Write(e.Message);
        }
        
    }
    public DataTable Get(string sqlstring)
    {
        MySqlDataAdapter dr = new MySqlDataAdapter(sqlstring, mysqlcon);
        DataTable tb = new DataTable();
        dr.Fill(tb);
        mysqlcon.Close();
        return tb;
    }
    public DataSet Get(string sqlstring,string tableid)
    {
        MySqlDataAdapter dr = new MySqlDataAdapter(sqlstring, mysqlcon);
        DataTable tb = new DataTable(tableid);
        dr.Fill(tb);
        ds.Tables.Add(tb);
        mysqlcon.Close();
        return ds;
    }
    public bool Execte(string sqlstring)
    {
        MySqlCommand cmd = new MySqlCommand(sqlstring, mysqlcon);
        mysqlcon.Open();
        int rowsReturned=cmd.ExecuteNonQuery();
        if (rowsReturned > 0)
        {
            mysqlcon.Close();
            return true;
        }
        else {
            mysqlcon.Close();
            return false;
        }

    }
    public int count(string sqlstring)
    {
        MySqlCommand cmd = new MySqlCommand(sqlstring, mysqlcon);
        mysqlcon.Open();
        int rowcount = Convert.ToInt32(cmd.ExecuteScalar());
        mysqlcon.Close();
        return rowcount;

    }
    public string JsonData(string sql) {
        DB DBConn = new DB();
        DataTable tb = DBConn.Get(sql);
        DBConn.mysqlcon.Close();
        return JsonHelper.ToJson(tb).ToString();
    }
    /// <summary>
    /// 执行事务
    /// </summary>
    /// <param name="sqlList">SQL语句列表</param>
    /// <returns></returns>
    public List<string> ExecuteTransaction(List<string> SqlList)
    {
        
        int i = 0;
        List<string> rst = new List<string>();
        mysqlcon.Open();
        //...初始化连接   
        //   开启事务  
        MySqlTransaction sqlTransaction = mysqlcon.BeginTransaction();
        //   将事务应用于Command   
        MySqlCommand sqlCommand = new MySqlCommand();
        sqlCommand.Connection = mysqlcon;
        sqlCommand.Transaction = sqlTransaction;
        try
        {
            //   利用sqlcommand进行数据操作
            foreach (string L in SqlList)
            {
                if (L != "SELECT FOUND_ROWS();")
                {
                    TableName = "Record-" +i++;
                    sqlCommand.CommandText =L;
                    MySqlDataAdapter dr = new MySqlDataAdapter(sqlCommand);
                    DataTable tb = new DataTable(TableName);
                    dr.Fill(tb);
                    rst.Add("\"" + TableName + "\": " + JsonHelper.ToJson(tb).ToString());
                }
                else
                {
                    sqlCommand.CommandText =L;
                    rst.Add("\"PageCount\": " + sqlCommand.ExecuteScalar().ToString());
                }
            }

            //   成功提交  
            sqlTransaction.Commit();
        }
        catch (Exception ex)
        {
            //   出错回滚   
            //rst = null;
            sqlTransaction.Rollback();
        }
        finally
        {
            mysqlcon.Close();
            sqlTransaction.Dispose();
            mysqlcon.Dispose();
        }
        return rst;
    }
    public List<string> BindTransaction(List<string> SqlList) {
        int i = 0;
        List<string> rst = new List<string>();
        mysqlcon.Open();
        //...初始化连接   
        //   开启事务  
        MySqlTransaction sqlTransaction = mysqlcon.BeginTransaction();
        //   将事务应用于Command   
        MySqlCommand sqlCommand = new MySqlCommand();
        sqlCommand.Connection = mysqlcon;
        sqlCommand.Transaction = sqlTransaction;
        try
        {
            //   利用sqlcommand进行数据操作   
            foreach (string strsql in SqlList)
            {
                TableName = "Record-" + i++;
                MySqlDataAdapter dr = new MySqlDataAdapter(strsql, mysqlcon);
                DataTable tb = new DataTable(TableName);
                dr.Fill(tb);
                rst.Add("\"" + TableName + "\": " + JsonHelper.ToJson(tb).ToString());
                //sqlCommand.CommandText = strsql;
                //sqlCommand.ExecuteNonQuery();
            }
            //   成功提交  
            sqlTransaction.Commit();
        }
        catch (Exception ex)
        {
            //   出错回滚   
            rst = null;
            sqlTransaction.Rollback();
        }
        finally
        {
            mysqlcon.Close();
            sqlTransaction.Dispose();
            mysqlcon.Dispose();
        }
        return rst;
    }


    //public bool ExecuteTransaction(List<string> SqlList)
    //{
    //    bool rst = true;
    //    mysqlcon.Open();
    //    //...初始化连接   
    //    //   开启事务  
    //    MySqlTransaction sqlTransaction = mysqlcon.BeginTransaction();
    //    //   将事务应用于Command   
    //    MySqlCommand sqlCommand = new MySqlCommand();
    //    sqlCommand.Connection = mysqlcon;
    //    sqlCommand.Transaction = sqlTransaction;
    //    try
    //    {
    //        //   利用sqlcommand进行数据操作   
    //        foreach (string strsql in SqlList)
    //        {
    //            MySqlDataAdapter dr = new MySqlDataAdapter(strsql, mysqlcon);
    //            DataTable tb = new DataTable();
    //            dr.Fill(tb);
    //            ds.Tables.Add(tb);
    //            //sqlCommand.CommandText = strsql;
    //            //sqlCommand.ExecuteNonQuery();
    //        }
    //        //   成功提交  
    //        sqlTransaction.Commit();
    //    }
    //    catch (Exception ex)
    //    {
    //        //   出错回滚   
    //        rst = false;
    //        sqlTransaction.Rollback();
    //    }
    //    finally
    //    {
    //        mysqlcon.Close();
    //        sqlTransaction.Dispose();
    //        mysqlcon.Dispose();
    //    }
    //    return rst;
    //}

}